--USE [RD4_DWCRWork]
--GO

--UPDATE [dbo].[JDEGSUDCVals_YCHOI]
--   SET [UDCID] = <UDCID, bigint,>
--      ,[SourceID] = <SourceID, int,>
--      ,[Source] = <Source, char(20),>
--      ,[InstallSystemCode] = <InstallSystemCode, char(4),>
--      ,[UserDefinedCode] = <UserDefinedCode, char(2),>
--      ,[UserDefinedCodesValues] = <UserDefinedCodesValues, varchar(10),>
--      ,[Description01] = <Description01, char(30),>
--      ,[CreateDate] = <CreateDate, datetime,>
-- WHERE <Search Conditions,,>
--GO


USE [RD4_DWCRWork]
GO

Create procedure SP_UpdateUDCDesconJDEGSUDCVALs
as
begin
	-- check if data exist then clear the table

	if exists (select 1 from [RD4_DWCRWork].[dbo].[JDEGSUDCVals_YCHOI])
	Begin
		Print 'Truncating data on [RD4_DWCRWork].[dbo].[JDEGSUDCVals_YCHOI]'; 
		Truncate table [RD4_DWCRWork].[dbo].[JDEGSUDCVals_YCHOI];
	end
	-- insert data from UDC table from RD4_DWCR
	Print 'adding records now';
	Insert [RD4_DWCRWork].[dbo].[JDEGSUDCVals_YCHOI]
	SELECT
			 ROW_NUMBER() over (Order By[Source])  as [UDCID]
			,[SourceID]
		  ,[Source]
		  ,[InstallSystemCode]
		  ,[UserDefinedCode]
		  ,LTRIM(RTRIM([UserDefinedCodesValues])) as [UserDefinedCodesValues]
		  ,[Description01]
		  ,getdate() as [CreateDate]
	  FROM [RD4_DWCR].[dbo].[JDEGSUserDefinedCodes]
	  PRINT 'Row counts are ' + cast (@@ROWCOUNT as Varchar(8));
Print 'Starting rebuilding index';
ALTER INDEX PK_Employee_BusinessEntityID ON [RD4_DWCRWork].[dbo].[JDEGSUDCVals_YCHOI]
REBUILD WITH (FILLFACTOR = 80, STATISTICS_NORECOMPUTE = ON);
Print 'Rebuilding Index is completed'

END


